package com.turing.post.organization.impl;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.organization.util.MerchantContract;
import com.turing.post.organization.util.MerchantContractDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "organization.impl.MerchantContractDaoImpl")
@Remote
@Local
public class MerchantContractDaoImpl implements MerchantContractDao {

	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@Override
	public MerchantContract getMerchantContractById(BigInteger mctId) {
		return _em.find(MerchantContract.class, mctId);
	}

	@Override
	public MerchantContract getMerchantContractByIdForUpdate(BigInteger mctId) {
		Query query = _em
				.createNativeQuery(
						"select * from MerchantContract t where t.contractId=?1 for update",
						MerchantContract.class);
		query.setParameter(1, mctId);
		return (MerchantContract) query.getSingleResult();
	}

	@Override
	public void insert(MerchantContract mct) throws PCPSApplicationException {
		GeneralDao.insert(_em, mct);
	}

	@Override
	public void update(MerchantContract mct) throws PCPSApplicationException {
		GeneralDao.update(_em, mct);
	}

	@Override
	public void delete(MerchantContract mct) throws PCPSApplicationException {
		GeneralDao.remove(_em, mct);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantContract> getMerchantContractByConditions(
			PCPSWebInput webinput, String contractTitle, String contractNo,
			String startDate, String endDate, String status, Integer page) {
		if (webinput == null || webinput.getOperBranchNo() == null) {
			return null;
		}
		String issueCompanyNo = webinput.getOperBranchNo().substring(0, 6);
		String isssql = " t.merchantno=m.merchantno and m.issuecompanyno='"
				+ issueCompanyNo + "' ";
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;

		String contractTitleCond = "";
		if (!PCPSUtil.isEmpty(contractTitle)) {
			contractTitleCond = " and contracttitle like '%" + contractTitle
					+ "%'";
		}
		String contractNoCond = "";
		if (!PCPSUtil.isEmpty(contractNo)) {
			contractNoCond = " and contractno like '%" + contractNo + "%'";
		}
		String sql = "select * from MerchantContract t,merchant m " + "where "
				+ isssql + contractTitleCond + contractNoCond;
		if (!PCPSUtil.isEmpty(startDate) && !PCPSUtil.isEmpty(endDate)) {
			// sql += "and (t.StartDate between to_date(?1,'yyyy-MM-dd') and
			// to_date(?2,'yyyy-MM-dd') or t.EndDate between
			// to_date(?1,'yyyy-MM-dd') and to_date(?2,'yyyy-MM-dd')) ";
			sql += "and (t.createTime >= to_date(?1,'yyyy-MM-dd hh24:mi:ss') and t.createTime <=to_date(?2,'yyyy-MM-dd hh24:mi:ss')) ";
		}
		if (!PCPSUtil.isEmpty(status)) {
			if (status.equals(MerchantContract.STATUS_WAIT_AUTH)) {
				sql += "and (t.status ='"
						+ MerchantContract.STATUS_INVALID_WAIT_AUTH
						+ "' or t.status ='"
						+ MerchantContract.STATUS_VALID_WAIT_AUTH + "') ";
			} else {
				sql += "and t.status ='" + status + "' ";
			}
		}
		sql += "order by t.createtime desc";

		Query query = _em.createNativeQuery(sql, MerchantContract.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		if (!PCPSUtil.isEmpty(startDate) && !PCPSUtil.isEmpty(endDate)) {
			if (!PCPSUtil.isEmpty(startDate)) {
				query.setParameter(1, startDate + " 00:00:00");
			}
			if (!PCPSUtil.isEmpty(endDate)) {
				query.setParameter(2, endDate + " 23:59:59");
			}
		}
		return query.getResultList();
	}

	@Override
	public BigInteger getMerchantContractTotalByConditions(
			PCPSWebInput webinput, String contractTitle, String contractNo,
			String startDate, String endDate, String status) {
		if (webinput == null || webinput.getOperBranchNo() == null) {
			return BigInteger.ZERO;
		}
		String issueCompanyNo = webinput.getOperBranchNo().substring(0, 6);
		String isssql = " t.merchantno=m.merchantno and m.issuecompanyno='"
				+ issueCompanyNo + "'";

		String contractTitleCond = "";
		if (!PCPSUtil.isEmpty(contractTitle)) {
			contractTitleCond = " and contracttitle like '%" + contractTitle
					+ "%'";
		}
		String contractNoCond = "";
		if (!PCPSUtil.isEmpty(contractNo)) {
			contractNoCond = " and contractno like '%" + contractNo + "%'";
		}
		String sql = "select nvl(count(*),0) from MerchantContract t,merchant m "
				+ "where " + isssql + contractTitleCond + contractNoCond;
		if (!PCPSUtil.isEmpty(startDate) && !PCPSUtil.isEmpty(endDate)) {
			// sql += " and (t.StartDate between to_date(?1,'yyyy-MM-dd') and
			// to_date(?2,'yyyy-MM-dd') or t.EndDate between
			// to_date(?1,'yyyy-MM-dd') and to_date(?2,'yyyy-MM-dd')) ";
			sql += "and t.createTime >= to_date(?1,'yyyy-MM-dd hh24:mi:ss') and t.createTime <=to_date(?2,'yyyy-MM-dd hh24:mi:ss')";
		}
		if (!PCPSUtil.isEmpty(status)) {
			if (status.equals(MerchantContract.STATUS_WAIT_AUTH)) {
				sql += "and (t.status ='"
						+ MerchantContract.STATUS_INVALID_WAIT_AUTH
						+ "' or t.status ='"
						+ MerchantContract.STATUS_VALID_WAIT_AUTH + "') ";
			} else {
				sql += "and t.status ='" + status + "' ";
			}
		}
		Query query = _em.createNativeQuery(sql);
		if (!PCPSUtil.isEmpty(startDate) && !PCPSUtil.isEmpty(endDate)) {
			if (!PCPSUtil.isEmpty(startDate)) {
				query.setParameter(1, startDate + " 00:00:00");
			}
			if (!PCPSUtil.isEmpty(endDate)) {
				query.setParameter(2, endDate + " 23:59:59");
			}
		}
		return new BigInteger(query.getSingleResult().toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantContract> getMerchantContractDropDownList(
			String merchantNo) {
		String sql = "select t.* from merchantcontract t where t.merchantno = ?1";
		Query query = _em.createNativeQuery(sql, MerchantContract.class);
		query.setParameter(1, merchantNo);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantContract> getMerchantContractRemind(
			PCPSWebInput webinput, String startDate, String endDate) {
		if (webinput == null || webinput.getOperBranchNo() == null) {
			return null;
		}
		String issueCompanyNo = webinput.getOperBranchNo().substring(0, 6);
		String sql = "select t.* from MerchantContract t,merchant m "
				+ "where t.merchantno=m.merchantno and m.issuecompanyno= ?1 "
				+ " and t.status= ?2 "
				+ " and t.ENDDATE <= to_date(?3,'yyyy-MM-dd hh24:mi:ss') "
				+ " order by t.createtime desc";
		Query query = _em.createNativeQuery(sql, MerchantContract.class);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, MerchantContract.STATUS_VALID);
		query.setParameter(3, startDate + " 23:59:59");

		return query.getResultList();
	}

	@Override
	public void merchantContractExtension(BigInteger extensionID,
			BigInteger extensionMonth) {
		String sql = "UPDATE MERCHANTCONTRACT SET ENDDATE = ADD_MONTHS(ENDDATE, ?1) WHERE CONTRACTID = ?2";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, extensionMonth);
		query.setParameter(2, extensionID);
		query.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantContract> getMCByMrchNoAndDateOverlapping(
			String merchantNo, Date startDate, Date endDate) {
		String sql = "select * from merchantcontract t"
				+ "  where t.merchantno = ?1 and t.status in('1','2')"
				+ " and ("
				+ " (to_date(?2,'yyyy-MM-dd') > t.startdate and to_date(?3,'yyyy-MM-dd') < t.enddate)"
				+ " or"
				+ " (to_date(?2,'yyyy-MM-dd') < t.startdate and to_date(?3,'yyyy-MM-dd') = t.startdate)"
				+ " or"
				+ " (to_date(?2,'yyyy-MM-dd') = t.startdate and to_date(?3,'yyyy-MM-dd') <> t.enddate)"
				+ " or"
				+ " (to_date(?2,'yyyy-MM-dd') <> t.startdate and to_date(?3,'yyyy-MM-dd') = t.enddate)"
				+ " or"
				+ " (to_date(?2,'yyyy-MM-dd') = t.enddate and to_date(?3,'yyyy-MM-dd') > t.enddate)"
				+ " or"
				+ " (to_date(?2,'yyyy-MM-dd') < t.startdate and to_date(?3,'yyyy-MM-dd') > t.enddate)"
				+ " or"
				+ " (to_date(?3, 'yyyy-MM-dd') <= t.enddate and to_date(?3, 'yyyy-MM-dd') >= t.startdate)"
				+ " or"
				+ " (to_date(?2, 'yyyy-MM-dd') >= t.startdate and to_date(?2, 'yyyy-MM-dd') <= t.enddate)"
				+ " )";
		Query query = _em.createNativeQuery(sql, MerchantContract.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, PCPSUtil.getStringYMDByDate(startDate));
		query.setParameter(3, PCPSUtil.getStringYMDByDate(endDate));
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantContract> checkMerchantContractNeedBusinessByMrchNo(
			String merchantNo) {
		String sql = "select * from merchantcontract t where t.merchantno = ?1"
				+ " and t.status in ('1','2') and t.policy = '3'";
		Query query = _em.createNativeQuery(sql, MerchantContract.class);
		query.setParameter(1, merchantNo);
		return query.getResultList();
	}

}
